package sueldos;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.JFileChooser;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;

import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WriteException;

import org.apache.commons.io.FilenameUtils;

public class  renatre
{
	public static void fileHeader(WritableSheet s) throws WriteException, SQLException{
		WritableFont wf = new WritableFont(WritableFont.ARIAL,10, WritableFont.BOLD);
		  WritableCellFormat cf = new WritableCellFormat(wf);
		  cf.setWrap(false);

		  Label l;
		  l = new Label(0,0,"fecha_de_ingreso");
		  s.addCell(l);
		  l = new Label(1,0,"tarea_realizada");
		  s.addCell(l);
		  l = new Label(2,0,"trab");
		  s.addCell(l);
		  l = new Label(3,0,"periodo1");
		  s.addCell(l);
		  l = new Label(4,0,"dias_periodo1");
		  s.addCell(l);
		  l = new Label(5,0,"rem_suj_aportes_1");
		  s.addCell(l);
		  l = new Label(6,0,"periodo2");
		  s.addCell(l);
		  l = new Label(7,0,"dias_periodo2");
		  s.addCell(l);
		  l = new Label(8,0,"rem_suj_aportes_2");
		  s.addCell(l);
		  l = new Label(9,0,"periodo3");
		  s.addCell(l);
		  l = new Label(10,0,"dias_periodo3");
		  s.addCell(l);
		  l = new Label(11,0,"rem_suj_aportes_3");
		  s.addCell(l);
		  l = new Label(12,0,"conv_corresp");
		  s.addCell(l);
		  l = new Label(13,0,"fecha_de_cese");
		  s.addCell(l);
		  l = new Label(14,0,"cuil");
		  s.addCell(l);
		  l = new Label(15,0,"cuit");
		  s.addCell(l);
		  l = new Label(16,0,"razon_social");
		  s.addCell(l);
		  l = new Label(17,0,"check");
		  s.addCell(l);
		  l = new Label(18,0,"formulario");
		  s.addCell(l);
		  l = new Label(19,0,"Fecha_formulario");
		  s.addCell(l);
		  l = new Label(20,0,"Delegacion");
		  s.addCell(l);
		  l = new Label(21,0,"control");
		  s.addCell(l);
		  l = new Label(22,0,"tipo_ingreso");
		  s.addCell(l);
		  l = new Label(23,0,"legajo");
		  s.addCell(l);
		  l = new Label(24,0,"apyn_trabajador");
		  s.addCell(l);
		  l = new Label(25,0,"t_formulario");
		  s.addCell(l);
		  l = new Label(26,0,"lugar");
		  s.addCell(l);
		  l = new Label(27,0,"renatre");
		  s.addCell(l);
		  l = new Label(28,0,"code");
		  s.addCell(l);
		  l = new Label(29,0,"frmlrenatre");
		  s.addCell(l);
		  l = new Label(30,0,"localidad");
		  s.addCell(l);
		  l = new Label(31,0,"provincia");
		  s.addCell(l);
		  l = new Label(32,0,"flr1");
		  s.addCell(l);
		  l = new Label(33,0,"flr2");
		  s.addCell(l);

	}
	
	
	public ResultSet renatreConsulta(int legajo, String periodo, String base){
		try {
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+base, "", ""); 
			String query="CALL aportes_Trimestrales(?,?)";
			PreparedStatement ps = conexion.prepareStatement(query);
			ps.setInt(1, legajo);
			ps.setString(2, periodo);
			ps.execute();
			ps.clearParameters();
			String sql = "select legajo, DATE_FORMAT(periodo1,'%m/%Y') AS fecha1 ,diasT1,aportes1," +
					"DATE_FORMAT(periodo2,'%m/%Y') AS fecha2 ,diasT2, aportes2, DATE_FORMAT(periodo3,'%m/%Y') AS fecha3 " +
					",diasT3,aportes3,DATE_FORMAT(ingreso,'%d/%m/%Y'),cuil,tarea,nombre,ano,situacion from aportes order by legajo asc";
			ps = conexion.prepareStatement(sql);	
			ps.execute();
			return ps.getResultSet();
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	
	
	public void writeCSV(int empresa, String periodo, ArrayList<Integer> empleado){
		String[] header = new String[] {"Cuil","fecha_de_ingreso","tarea_realizada","condicion_laboral","conv_corresp","fecha_de_cese","cuit","razon_social","establecimiento","provincia","departamento","localidad","periodo1","dias_periodo1","rem_suj_aportes_1","periodo2","dias_periodo2","rem_suj_aportes_2","periodo3","dias_periodo3","rem_suj_aportes_3"};
		
		FileOutputStream fout;
		try {
			
			Empresa emp = new Empresa();
			emp.getDataDB(empresa);

			
	       	UIManager.setLookAndFeel(
                    UIManager.getSystemLookAndFeelClassName());
        	
        	JFileChooser fc = new JFileChooser();


			
			//String directorio = "E:/75 - RENATRE/ImportacionCupones/";
			
        	fc.setCurrentDirectory(new File("E:/75 - RENATRE/ImportacionCupones/"));
        	fc.showSaveDialog(fc);
        	

        	String basename = FilenameUtils.getBaseName(fc.getSelectedFile().getName());
        	//String extension = "csv"; 

			fout = new FileOutputStream(fc.getSelectedFile().getParentFile().toString().concat("/").concat(basename).concat(".csv"));
	        OutputStreamWriter writer = new OutputStreamWriter(fout, "ISO-8859-1");
			
	        System.out.println(fc.getSelectedFile().getParentFile().toString().concat("/").concat(basename).concat(".csv"));
	        for (int i = 0; i < header.length; i++) {
	        	writer.append(header[i].toString());
	        	writer.append(",");
			}
	        
	        writer.write("\n");

	        
	        for (int k = 0; k < empleado.size(); k++) {
				
				
			ResultSet rs = this.renatreConsulta(Integer.valueOf(empleado.get(k)),periodo, emp.getBase());
			
			rs.next();
						
			

	        rs.beforeFirst();
	        
	        while ( rs.next()) {

				//CUIL
				String cuilSG = rs.getString(12);
				writer.append(cuilSG.substring(0, 2)+cuilSG.substring(3, 11)+cuilSG.substring(12));
	        	writer.append(",");
				
				writer.append(rs.getString(11)); //fecha de ingreso
	        	writer.append(",");

	        	writer.append(rs.getString(13).toUpperCase()); //tarea
				writer.append(",");
				
			    writer.append(rs.getString(16).toUpperCase()); //MODALIDAD DE CONTRATACION
	        	writer.append(",");

				//Convenio
				writer.append("NO");
	        	writer.append(",");

	        	//fecha de cese
	        	writer.append(",");
	        	
				//CUIT
				writer.append(emp.getCuitSG());
	        	writer.append(",");

				//razon social
				writer.append(emp.getNombre().toUpperCase());
	        	writer.append(",");

				//establecimiento
				writer.append(emp.getEstablecimiento().toUpperCase());
	        	writer.append(",");

	        	//provincia
	        	writer.append(emp.getProvincia().toUpperCase());
	        	writer.append(",");
	        	
	        	//depto
	        	writer.append(emp.getDepartamento().toUpperCase());
	        	writer.append(",");
	        	
				//localidad
	        	writer.append(emp.getLocalidad().toUpperCase());
	        	writer.append(",");

	        	
				 
				//periodo 1 
			    writer.append(rs.getString(2)); //periodo 1
	        	writer.append(",");
			    writer.append(rs.getString(3)); // dias trabajados 1
	        	writer.append(",");
				writer.append(rs.getString(4)); // importe bruto 1
	        	writer.append(",");
				//Periodo 2 

	        	if (rs.getString(5) != null){
	        		writer.append(rs.getString(5));  //periodo 2
	        	} else {
	        		writer.append("");  
	        	}
	        	
	        	writer.append(",");
				
	        	if (rs.getString(6) != null){
	        		writer.append(rs.getString(6));  //dias trabajados 2
	        	} else {
	        		writer.append(""); 
	        	}
	        	
	        	writer.append(",");
				
	        	if (rs.getString(7) != null){
	        		writer.append(rs.getString(7));  //importe bruto 2
	        	} else {
	        		writer.append("");  
	        	}
	        	
	        	writer.append(",");

				//Periodo 3
	        	if (rs.getString(8) != null){
	        		writer.append(rs.getString(8));  //periodo 3
	        	} else {
	        		writer.append("");  
	        	}

	        	writer.append(",");
	        	
	        	if (rs.getString(9) != null){
	        		writer.append(rs.getString(9));  //dias trabajados 3
	        	} else {
	        		writer.append(""); 
	        	}
				
	        	writer.append(",");
	        	
	        	if (rs.getString(10) != null){
	        		writer.append(rs.getString(10));  //importe bruto 3
	        	} else {
	        		writer.append("");  
	        	}
				
	        	writer.append(",");
	        	
	        	
	        	writer.write("\n");//fin de linea

				
				
				
				
				
				
			}// end while

			
		
			//--
	        
	        
	        writer.flush();
	        //writer.close();
			}
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedLookAndFeelException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	

	@SuppressWarnings("unused")
	private  void writeDataSheet(WritableSheet s, int empresa, String fecha, String base, int[] vector) throws WriteException, SQLException{
		WritableFont wf = new WritableFont(WritableFont.ARIAL,10, WritableFont.BOLD);
		WritableCellFormat cf = new WritableCellFormat(wf);
		cf.setWrap(false);

		
		Empresa emp = new Empresa();
		emp.getDataDB(empresa);

		
		Label l;


		int i=1;

		
		
   
		int[] j = vector;
		
		for (int k = 0; k < j.length; k++) {
			
			
		ResultSet rs = this.renatreConsulta(j[k],fecha, base);
		
		rs.beforeFirst();
		while ( rs.next()) {
			l = new Label(0,i,rs.getString(11)); //fecha de ingreso
			s.addCell(l);
			
		    l = new Label(1,i,rs.getString(13)); //tarea
		    s.addCell(l);
		   
		    l = new Label(2,i,rs.getString(16)); //MODALIDAD DE CONTRATACION
			s.addCell(l);
		
			 
			//periodo 1 
			l = new Label(3,i,rs.getString(2)); //periodo 1
			s.addCell(l);
			l = new Label(4,i,rs.getString(3)); // dias trabajados 1
			s.addCell(l);
			l = new Label(5,i,rs.getString(4)); // importe bruto 1
			s.addCell(l);
			//Periodo 2 
			l = new Label(6,i,rs.getString(5)); //periodo 2
			s.addCell(l);
			l = new Label(7,i,rs.getString(6)); //dias trabajados 2
			s.addCell(l);
			l = new Label(8,i,rs.getString(7)); // importe bruto 2
			s.addCell(l);

			//Periodo 3
			l = new Label(9,i,rs.getString(8));  //periodo 3
			s.addCell(l);
			l = new Label(10,i,rs.getString(9)); //dias trabajados 3
			s.addCell(l);
			l = new Label(11,i,rs.getString(10)); // importe bruto 3
			s.addCell(l);

			//Convenio
			l = new Label(12,i,"NO");
			s.addCell(l);

			//CUIL
			String cuilSG = rs.getString(12);
			
			l = new Label(14,i,cuilSG.substring(0, 2)+cuilSG.substring(3, 11)+cuilSG.substring(12));
			s.addCell(l);

			//CUIT
			l = new Label(15,i, emp.getCuitSG());
			s.addCell(l);
			
			//Nombre
			l = new Label(16,i, emp.getNombre());
			s.addCell(l);
			
			

			//Legajo y Nombre
			l = new Label(23,i,rs.getString(1));
			s.addCell(l);
			l = new Label(24,i,rs.getString(14));
			s.addCell(l);


			
			l = new Label(30,i,emp.getLocalidad());
			s.addCell(l);
			l = new Label(31,i,emp.getProvincia());
			s.addCell(l);
			
			
			
			
			
			i++;
		}// end while
		
		}// end for
		
	}

	//{150,160,162,172,180,191} CLAVIER BM
	
	//{64,71,74,77} aztiria
			
			
	//{1,48,68,222,223,237,248,253,255,256,257,263,264}; CLAVIER LINCOLN
	
	//{33,57,90,93,94,100,104,105,119,120,121,122}
	
	

	/*
	public static void main(String[] arg) throws SQLException {
		try
		{
			int e = 2;
			String fecha = "2013-08-31";
			String base = "clavier";
			int[] vector = {48}; //114,160,162,172,180,191,197,222,237,248,253,254,255,256,257,258,261,262,263,273,275,283,288,291,292,293,553,556,557,558};
			String filename = "c:/Renatre - Cupones - "+base+".xls";
			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("ar", "ES"));
			WritableWorkbook workbook = 
				Workbook.createWorkbook(new File(filename), ws);
			WritableSheet s = workbook.createSheet("Renatre", 0);
			fileHeader(s);
			//this.writeDataSheet(s,e,fecha, base, vector);
			workbook.write();
			workbook.close();      
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		catch (WriteException e)
		{
			e.printStackTrace();
		}
	}// fin main
	*/


}



